USE [Test_CorporateData]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetCompanyProfiles]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetCompanyProfiles]
GO

CREATE PROCEDURE [dbo].[spGetCompanyProfiles]
AS
SELECT	dbo.Companies.Id AS CompanyId, 
		dbo.Companies.Name AS CompanyName, 
		dbo.Companies.Description AS CompanyDescription, 
		CAST(dbo.Countries.Name AS NVARCHAR(50)) AS Country, 
		CAST(dbo.PeerGroups.Name AS NVARCHAR(50)) AS ShortPeerGroupName,
        CAST(dbo.ISINs.Value AS NVARCHAR(50)) AS MainISIN, 
		CAST(dbo.Sedols.Value AS NVARCHAR(50)) AS MainSedol, 
		CAST(dbo.Tickers.Value AS NVARCHAR(50)) AS MainTicker, 
		dbo.Companies.LastUpdated AS LastCorporateDataUpdate
FROM    dbo.Companies INNER JOIN
        dbo.Countries ON dbo.Companies.CountryId = dbo.Countries.Id INNER JOIN
        dbo.PeerGroups ON dbo.Companies.PeerGroupId = dbo.PeerGroups.Id LEFT OUTER JOIN
        dbo.Tickers ON dbo.Companies.MainTickerId = dbo.Tickers.Id LEFT OUTER JOIN
        dbo.Sedols ON dbo.Companies.MainSedolId = dbo.Sedols.Id LEFT OUTER JOIN
        dbo.ISINs ON dbo.Companies.MainISINId = dbo.ISINs.Id
	
RETURN 0

GO


